using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SelectManyModule_Application(SqlString Username)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string selectManyModuleApplicationCommandText =
                @"
                    SELECT 
                        MA.ApplicationGuid, MA.ApplicationId, MA.ApplicationName, MA.ApplicationDescription, MA.ApplicationOrder

                    FROM 
                        Module_Application MA

                    WHERE
                        ApplicationGuid IN 
                        (
                            SELECT DISTINCT
                                SAR.ApplicationGuid

                            FROM 
                                Security_ApplicationRole SAR

                            INNER JOIN
                                Security_UserRole SUR
                                ON SAR.RoleGuid = SUR.RoleGuid

                            INNER JOIN
                                Security_User SU
                                ON SUR.UserGuid = SU.UserGuid

                            WHERE 
                                SU.UserName = @Username
                        )
                ";

                SqlCommand selectManyModuleApplicationCommand = new SqlCommand(selectManyModuleApplicationCommandText, connection);

                selectManyModuleApplicationCommand.Parameters.Add(new SqlParameter("@Username", Username));

                SqlContext.Pipe.ExecuteAndSend(selectManyModuleApplicationCommand);
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
